package com.path.planning.mapper;

import com.path.planning.entity.RoutingNetwork;
import com.path.planning.entity.RoutingCountry;
import com.path.planning.entity.RoutingResult;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.locationtech.jts.geom.Point;

import java.util.List;

/**
 * 路径规划数据访问层 - 多国家独立表结构
 */
@Mapper
public interface RoutingMapper {

    /**
     * 根据ID查找路网段
     */
    @Select("SELECT * FROM ${tableName} WHERE id = #{id}")
    RoutingNetwork findById(@Param("tableName") String tableName, @Param("id") Long id);

    /**
     * 根据ID列表查找路网段
     */
    @Select("<script>" +
            "SELECT * FROM ${tableName} WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<RoutingNetwork> findByIds(@Param("tableName") String tableName, @Param("ids") List<Long> ids);

    /**
     * 查找指定半径内的路网段
     */
    @Select("SELECT *, ST_Distance(geom, #{point}) as distance FROM ${tableName} " +
            "WHERE ST_DWithin(geom, #{point}, #{radius}) " +
            "ORDER BY distance LIMIT #{limit}")
    List<RoutingNetwork> findInRadius(@Param("tableName") String tableName,
                                     @Param("point") Point point, 
                                     @Param("radius") Double radius, 
                                     @Param("limit") Integer limit);

    /**
     * 查找最近的路网段
     */
    @Select("SELECT *, ST_Distance(geom, #{point}) as distance FROM ${tableName} " +
            "WHERE ST_DWithin(geom, #{point}, #{radius}) " +
            "ORDER BY distance LIMIT 1")
    RoutingNetwork findNearest(@Param("tableName") String tableName,
                              @Param("point") Point point, 
                              @Param("radius") Double radius);

    /**
     * 根据起点ID查找路网段
     */
    @Select("SELECT * FROM ${tableName} WHERE source = #{sourceId}")
    List<RoutingNetwork> findBySource(@Param("tableName") String tableName, @Param("sourceId") Long sourceId);

    /**
     * 根据终点ID查找路网段
     */
    @Select("SELECT * FROM ${tableName} WHERE target = #{targetId}")
    List<RoutingNetwork> findByTarget(@Param("tableName") String tableName, @Param("targetId") Long targetId);

    /**
     * 根据起点或终点ID查找路网段
     */
    @Select("SELECT * FROM ${tableName} WHERE source = #{nodeId} OR target = #{nodeId}")
    List<RoutingNetwork> findByNodeId(@Param("tableName") String tableName, @Param("nodeId") Long nodeId);

    /**
     * 使用数据库函数查找最近节点
     */
    @Select("SELECT find_nearest_node(#{point}, #{countryCode}, #{radius})")
    Long findNearestNodeId(@Param("point") Point point, 
                           @Param("countryCode") String countryCode, 
                           @Param("radius") Double radius);

    /**
     * 获取所有支持的国家
     */
    @Select("SELECT * FROM routing_countries WHERE is_active = true ORDER BY country_name")
    List<RoutingCountry> getAllCountries();

    /**
     * 根据国家代码获取国家信息
     */
    @Select("SELECT * FROM routing_countries WHERE country_code = #{countryCode}")
    RoutingCountry getCountryByCode(@Param("countryCode") String countryCode);

    /**
     * 根据国家代码获取表名
     */
    @Select("SELECT table_name FROM routing_countries WHERE country_code = #{countryCode}")
    String getTableNameByCountryCode(@Param("countryCode") String countryCode);

    /**
     * 获取路网统计信息
     */
    @Select("SELECT * FROM routing_network_stats WHERE country_code = #{countryCode}")
    NetworkStatistics getNetworkStatistics(@Param("countryCode") String countryCode);

    /**
     * 获取所有国家的路网统计信息
     */
    @Select("SELECT * FROM routing_network_stats ORDER BY country_name")
    List<NetworkStatistics> getAllNetworkStatistics();

    /**
     * 检查国家是否存在
     */
    @Select("SELECT EXISTS (SELECT 1 FROM routing_countries WHERE country_code = #{countryCode} AND is_active = true)")
    boolean countryExists(@Param("countryCode") String countryCode);

    /**
     * 获取国家路网段数量
     */
    @Select("SELECT COUNT(*) FROM ${tableName}")
    long getNetworkCount(@Param("tableName") String tableName);

    /**
     * 获取国家路网总长度
     */
    @Select("SELECT COALESCE(SUM(length), 0) FROM ${tableName}")
    double getTotalLength(@Param("tableName") String tableName);

    /**
     * 网络统计信息
     */
    class NetworkStatistics {
        private String countryCode;
        private String countryName;
        private String tableName;
        private long totalSegments;
        private double totalLength;

        // 构造函数
        public NetworkStatistics() {}

        // Getter和Setter方法
        public String getCountryCode() {
            return countryCode;
        }

        public void setCountryCode(String countryCode) {
            this.countryCode = countryCode;
        }

        public String getCountryName() {
            return countryName;
        }

        public void setCountryName(String countryName) {
            this.countryName = countryName;
        }

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public long getTotalSegments() {
            return totalSegments;
        }

        public void setTotalSegments(long totalSegments) {
            this.totalSegments = totalSegments;
        }

        public double getTotalLength() {
            return totalLength;
        }

        public void setTotalLength(double totalLength) {
            this.totalLength = totalLength;
        }

        @Override
        public String toString() {
            return "NetworkStatistics{" +
                    "countryCode='" + countryCode + '\'' +
                    ", countryName='" + countryName + '\'' +
                    ", tableName='" + tableName + '\'' +
                    ", totalSegments=" + totalSegments +
                    ", totalLength=" + totalLength +
                    '}';
        }
    }
}
